package com.roc.service.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.ChangeCheckCountBean;
import com.roc.newPage.IPageDao;
import com.roc.service.ServiceException;
import com.roc.service.count.ChangeCheckCountService;
import com.roc.util.Constance;

@Service
public class ChangeCheckCountServiceImpl implements ChangeCheckCountService{
	private Logger logger=LogManager.getLogger(ChangeCheckCountServiceImpl.class);
	@Autowired
	private IPageDao pageDao;
	
	@Transactional(propagation=Propagation.REQUIRED,readOnly=true)
	@Override
	public int queryChangeCheckCount(List<Object> params) {
		String sql = "  select count(1)  "
				+ " FROM(SELECT t.op_accountno,t.op_accountname,COUNT(t.op_accountname) total FROM t_change_receive t where 1=1 "
				+ " AND SUBSTR(t.createdate, 1, 10) >= ?  "
				+ " AND SUBSTR(t.createdate, 1, 10) <= ?  "
				+ " GROUP BY  t.op_accountname,t.op_accountno) a  "
				+ " LEFT JOIN ( "
				+ " SELECT sum(case when I.status = 'Y' then 1 else 0 end) passCount, "
				+ " sum(case when I.STATUS = 'N' then  1 else 0 end) noPassCount,I.Operators"
				+ " FROM T_MERINFO_CHANGE I "
				+ " where 1=1 "
				+ " AND TO_CHAR(I.DT_AUDITOR_TIME, 'yyyy-MM-dd') >= ?  "
				+ " AND TO_CHAR(I.DT_AUDITOR_TIME, 'yyyy-MM-dd') <= ?  "
				+ "GROUP BY I.Operators) b "
				
				+ " on a.op_accountname = b.Operators ";
		logger.info("变更审核数据数量查询sql："+sql);
		int rowCount = 0;
		try {
			rowCount = pageDao.countByParams(sql,params);
			logger.info("变更审核数据数量查询sql结果："+rowCount);
		} catch (Exception e) {
			logger.info("变更审核数据数量查询异常"+e);
		}
		return rowCount;
	}
	
	
	@Transactional(propagation=Propagation.REQUIRED,readOnly=true)
	@Override
	public List<ChangeCheckCountBean> queryChangeCheckList(List<Object> params,int calcuStart, int pageMaxSize)throws ServiceException {
		// TODO Auto-generated method stub
		String sql=" SELECT a.op_accountno,a.op_accountname,a.total,b.passCount,b.noPassCount  "
				+ " FROM(SELECT t.op_accountno,t.op_accountname,COUNT(t.op_accountname) total FROM t_change_receive t where 1=1 "
				+ " AND SUBSTR(t.createdate, 1, 10) >= ?  "
				+ " AND SUBSTR(t.createdate, 1, 10) <= ?  "
				+ " GROUP BY  t.op_accountname,t.op_accountno) a  "
				+ " LEFT JOIN ( "
				+ " SELECT sum(case when I.status = 'Y' then 1 else 0 end) passCount, "
				+ " sum(case when I.STATUS = 'N' then  1 else 0 end) noPassCount,I.Operators"
				+ " FROM T_MERINFO_CHANGE I "
				+ " where 1=1 "
				+ " AND TO_CHAR(I.DT_AUDITOR_TIME, 'yyyy-MM-dd') >= ?  "
				+ " AND TO_CHAR(I.DT_AUDITOR_TIME, 'yyyy-MM-dd') <= ?  "
				+ "GROUP BY I.Operators) b "
				+ " on a.op_accountname = b.Operators "
				+ " order by nvl(a.total, 0) desc, nvl(b.passCount, 0) desc  ";
		logger.info("查询sql:"+sql);
		List<Object> searchRsList=new ArrayList<Object>();
		List<ChangeCheckCountBean> ccList= new ArrayList<ChangeCheckCountBean>();
		try {
			searchRsList = pageDao.findAllByParams(sql, params, calcuStart,  pageMaxSize);
			logger.info("查询到记录："+searchRsList.size()+"条");
			ccList = dealTheListSpecially(ccList,searchRsList);
		} catch (Exception e) {
			logger.info("查询变更审核统计数据异常"+e);
		}
		return ccList;
	}


	private List<ChangeCheckCountBean> dealTheListSpecially(List<ChangeCheckCountBean> ccList, List<Object> searchRsList) throws ServiceException{
		if(searchRsList != null && searchRsList.size()>0){
			Iterator it = searchRsList.iterator();
			while(it.hasNext()){
				Object[] objArray = (Object[]) it.next();
				ChangeCheckCountBean change = new ChangeCheckCountBean();
				change.setAccountNo(Constance.Null2EmptyTrim(objArray[0]));
				change.setRealName(Constance.Null2EmptyTrim(objArray[1]));
				change.setTotal(Constance.null2Zero(objArray[2]));
				change.setPassCount(Constance.null2Zero(objArray[3]));
				change.setNoPassCount(Constance.null2Zero(objArray[4]));
				ccList.add(change);
			}
			
		}
		return ccList;
	}


	@Override
	public int queryChangeCheckAgentCount(List<Object> params)
			throws ServiceException {
		// TODO Auto-generated method stub
		return 0;
	}


	@Override
	public List<ChangeCheckCountBean> queryChangeChecAgentkList(
			List<Object> params, int calcuStart, int rows)
			throws ServiceException {
		// TODO Auto-generated method stub
		return null;
	}
}
